﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using University.Workshop.Model;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using System.Globalization;

namespace University.Workshop.DataAccess
{
    public static class CommentDataAccess
    {
        public const string ConnectionString = "Data Source=.\\sqlexpress; Initial Catalog=LQSdataBase; Integrated Security=true";
        public static Comment InsertComment(Comment comment)
        {
            if (comment != null)
            {
                SqlConnection connection = null;
                try
                {
                    connection = new SqlConnection(ConnectionString);
                    SqlCommand command = connection.CreateCommand();
                    command.CommandText = "INSERT INTO Comment(TopicId, UserId, Date,Text) VALUES(@TopicId,@UserId,@Date, @Text)";
                    command.Parameters.AddWithValue("TopicId", comment.TopicId);
                    command.Parameters.AddWithValue("UserId", comment.UserId);
                    command.Parameters.AddWithValue("Date", comment.Date);
                    command.Parameters.AddWithValue("Text", comment.Text);
                    SqlCommand getID = connection.CreateCommand();
                    getID.CommandText = "SELECT @@Identity";
                    connection.Open();
                    int result = command.ExecuteNonQuery();
                    if (result <= 0)
                    {
                        throw new DataAccessException("Error inserting comment");
                    }
                    comment.CommentId= Convert.ToInt64(getID.ExecuteScalar(), CultureInfo.CurrentCulture);
                    
                }
                
                catch (Exception er)
                {
                    throw new DataAccessException("Error inserting comment", er);
                }
                finally
                {
                    if (connection != null)
                    {
                        connection.Close();
                    }
                    SqlConnection.ClearAllPools();
                }
            }
            return comment;
        }

        public static Collection<Comment> SelectComments(Topic topic)
        {
            Collection<Comment> comments = new Collection<Comment>();
            
            if (topic != null)
            {
                SqlConnection connection = null;
                try
                {
                    connection = new SqlConnection(ConnectionString);
                    SqlCommand command = connection.CreateCommand();
                    command.CommandText = "SELECT * from Comment WHERE TopicId=@topicId";
                    command.Parameters.AddWithValue("topicId", topic.TopicId);
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader();
                    if (!reader.HasRows)
                    {
                        return null;
                    }
                    while (reader.Read())
                    {
                        Comment comment = new Comment();
                        comment.CommentId = Convert.ToInt64(reader["CommentId"].ToString(), CultureInfo.CurrentCulture);
                        comment.UserId = Convert.ToInt64(reader["UserId"].ToString(), CultureInfo.CurrentCulture);
                        comment.TopicId = Convert.ToInt64(reader["TopicId"].ToString(), CultureInfo.CurrentCulture);
                        comment.Date = Convert.ToDateTime(reader["Date"].ToString(), CultureInfo.CurrentCulture);
                        comment.Text = reader["Text"].ToString();
                        //if (!Convert.IsDBNull(reader["Image"]))
                        //{
                        //    comment.Image = (byte[])reader["Image"];
                        //    comment.ImageLength = Convert.ToInt32(reader["ImageLength"].ToString(), CultureInfo.CurrentCulture);
                        //}
                        comments.Add(comment);
                    }
                }
                catch(Exception er)
                {
                    throw new DataAccessException("Error getting comments", er);
                }
                finally
                {
                    if (connection != null)
                    {
                        connection.Close();
                    }
                    SqlConnection.ClearAllPools();
                }
            }
            return comments;
        }
    }
}
